//
// Copyright (c) 2014, 保留所有权利。
//
// OrderSync.java
//
// 作    者：王红燕
// 当前版本：1.0
// 2014 下午1:15:41
//
// 
//
package com.vanke.agent.sync;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.Arrays;
import java.util.Date;

import org.apache.log4j.Logger;

import com.vanke.agent.ADataSource;
import com.vanke.agent.C;
import com.vanke.agent.Cfg;
import com.vanke.agent.bean.FlagLogBean;
import com.vanke.agent.bean.FollowRecord;
import com.vanke.agent.bean.RoomInfo;
import com.vanke.agent.common.CstUtil;
import com.vanke.agent.common.FlagLog;
import com.vanke.agent.common.RecommendUtil;
import com.vanke.agent.common.TradeUtil;
import com.vanke.agent.common.Util;
import com.vanke.agent.common.log.LoggerUtil;

/**
 * 根据修改时间增量同步认购记录<br>
 * 同步规则：<br>
 * <ul>
 * <li>1. 按修改时间取认购记录，起始时间为上次同步的记录的最后一条记录的修改时间，结束时间为当前时间</li>
 * <li>
 * 2. 根据OrderGUID判断记录在数据中心是否已经存在
 * 
 * <pre>
 * 	a. 若存在，则判断修改时间是否相同（精确到秒）
 * 	b. 若修改时间相同，则更新数据中心s_Order对应的记录，不做其它处理
 *  c. 若修改时间不同，则判断是否为退房、换房、作废、撤消，如果是，则下发通知；
 * </pre>
 * 
 * </li>
 * <li>3. 若不存在，则保存本条记录到数据中心s_Order表</li>
 * <li>4. 根据TradeGUID，关联s_Trade2Cst表，查询对应的客户</li>
 * <li>5. 保存对应s_Trade2Cst记录，产生通知</li>
 * </ul>
 */
public class OrderSync extends AgentSync {
	Logger log = LoggerUtil.getLoggerByName("order_sync");

	@Override
	public void sync() {
		long now = System.currentTimeMillis();
		int added = 0, updated = 0;
		int record = 0;

		String timeStr = dateFormat.format(Util.offMinute(orderCfg.beginTime,
				rebackOffset));
		String nowStr = dateFormat.format(new Date(System.currentTimeMillis()));
		//		orderCfg.beginTime = new java.sql.Date(System.currentTimeMillis());

		log.info("[03]同步Order");
		Connection con = ADataSource.getConnection(ADataSource.SCHEMA_MY);
		Connection wcon = ADataSource.getConnection(ADataSource.SCHEMA_BIZ);

		try {
			String fields = C.FIELDS_ORDER;
			String table = "s_Order";

			String querySql = "SELECT " + fields + "  FROM " + table + " where ";
			querySql += "(ModiDate>=? AND ModiDate<? AND ModiDate is not null)";
			querySql += " or (CloseDate>=? AND CloseDate<? AND ModiDate is not null and status='关闭') ";
			querySql += " order by ModiDate asc";
			log.info("execute: " + querySql + "[" + timeStr + "~" + nowStr
					+ "]");

			// 新增
			String insertSql = CstUtil.formatInsertSql(table, fields);
			PreparedStatement wpstmt = wcon.prepareStatement(insertSql);
			int columns = fields.split(",").length;

			// 更新
			String updateSql = CstUtil.formatUpdateSql(table, fields);
			updateSql += " where OrderGUID=?";
			PreparedStatement updatePstmt = wcon.prepareStatement(updateSql);

			PreparedStatement pstmt = con.prepareStatement(querySql,
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
			pstmt.setString(1, timeStr);
			pstmt.setString(2, nowStr);
			pstmt.setString(3, timeStr);
			pstmt.setString(4, nowStr);

			ResultSet rs = pstmt.executeQuery();

			if (!rs.next()) {
				log.info("no order is created");
				ADataSource.close(rs);
				ADataSource.close(pstmt);
				return;
			}
			do {
				record++;
				Timestamp modiDate = rs.getTimestamp("ModiDate");
				Timestamp closeDate = rs.getTimestamp("CloseDate");
				orderCfg.beginTime = new java.sql.Date(modiDate.getTime());
				String OrderGUID = rs.getString("OrderGUID");
				Object[] exists = exists(wcon, OrderGUID);
				Float updateMoney=0f;
				try {
					if (exists[0] != null) {
						// 本条记录在数据中心已存在，已存在分两种情况
						// 1. 在明源发生修改
						// 2. 因为回溯重复读取的数据
						log.info("order[" + OrderGUID
								+ "] aready exists in datacenter");
						for (int i = 1; i <= columns; i++) {
							updatePstmt.setString(i, rs.getString(i));
						}
						//=========修改交易前提前获取历史金额开始===============
						updateMoney=Float.valueOf(exists[1]+"");
						//=========修改交易前提前获取历史金额结束===============
						updatePstmt.setString(columns + 1, OrderGUID);
						updatePstmt.executeUpdate(); // 更新现有记录
						updated++;
						
						// 如果更新时间不同（精确到秒），则检查是否需要下发退房通知
						if (((Timestamp)exists[0]).getTime() / 1000 != modiDate.getTime() / 1000) { //取到秒
							checkNotice(rs,updateMoney); // 更新时间有变化
						} else if (closeDate != null) {
							if (exists[2] == null ||(exists[2]!=null && ((Timestamp)exists[2]).getTime() / 1000 != closeDate.getTime() / 1000)) {
								checkNotice(rs,updateMoney); // 关闭时间有变化
							}
						}
						continue;
					} // end if (exists) 
					// 查询处理关联客户
					boolean hasRefCst = syncTrade2Cst(rs, C.TRADE_STATUS_NEW, true, updateMoney);
					if (!hasRefCst) {
						log.info("SYNC_SAVE[02]认购客户在数据中心未找到[" + OrderGUID + "]");
						continue;
					}
					// 保存认购记录
					log.info("save order[" + OrderGUID + "]");
					for (int i = 1; i <= columns; i++) {
						wpstmt.setString(i, rs.getString(i));
					}
					wpstmt.executeUpdate();
					added++;
				} catch (Exception e) {
					log.error("save or update order error:" + e);
				}
			} while (rs.next());
			// 保存配置，最后一次的时间
			Cfg.set(orderCfg.cfgKey, dateFormat.format(orderCfg.beginTime));
			Cfg.save();

			// 关闭
			ADataSource.close(rs);
			ADataSource.close(pstmt);
			ADataSource.close(wpstmt);
		} catch (Exception e) {
			e.printStackTrace();
			log.error("sync order error:" + e);
		} finally {
			ADataSource.close(con);
			ADataSource.close(wcon);
		}
		now = System.currentTimeMillis() - now;
		log.info(added + " order added and " + updated + " updated, by "
				+ record + " records. spend " + now + " ms");
	} //~sync

	/*
	 * 检查更新时，是否为退房、换房等
	 */
	protected void checkNotice(ResultSet rs,Float updateMoney) throws SQLException {
		String reason = rs.getString("CloseReason");
		String OrderGUID = rs.getString("OrderGUID");
		log.info("OrderGUID:"+OrderGUID+",CloseReason:"+reason+",boolean:"+reason.equals(""));
		int state = TradeUtil.getTradeState(reason);
		
		log.info("[03]检查认购更新");
		if (state != C.TRADE_STATUS_NONE && state != C.TRADE_STATUS_NEW) {
			log.info("[03]发送认购更新通知");
			syncTrade2Cst(rs, state, false,updateMoney);
		}
	} //~checkNotice

	/**
	 * 同步处理认购交易关联
	 * @param queryRs 认购记录的当前指针
	 * @param state 修改状态 参见定义com.vanke.agent.C
	 * @param save 是否保存关联表记录
	 */
	protected boolean syncTrade2Cst(ResultSet queryRs, int state, boolean save,Float updateMoney) {
		//		String roomGUID, String tradeGUID,
		//		String projGUID, Timestamp time, float total, String reason
		Connection myCon = ADataSource.getConnection(ADataSource.SCHEMA_MY);
		PreparedStatement myPstmt = null;
		ResultSet rs = null;

		Connection bizCon = null;
		PreparedStatement bizPstmt = null;

		String fields = C.FIELDS_TRADE2CST;

		try {
			String roomGUID = queryRs.getString("RoomGUID");
			String tradeGUID = queryRs.getString("TradeGUID");
			String projGUID = queryRs.getString("ProjGUID");
			Timestamp time = queryRs.getTimestamp("CreatedOn");
			double total = queryRs.getDouble("Total");
			String reason = queryRs.getString("CloseReason");
			String OrderGUID = queryRs.getString("OrderGUID");
			// 查找关联表
			String sql = "select " + fields
					+ " from s_Trade2Cst  where TradeGUID=?";
			myPstmt = myCon.prepareStatement(sql,
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
			myPstmt.setString(1, tradeGUID);
			rs = myPstmt.executeQuery();

			// 如果没查到，记录日志，结束
			if (!rs.next()) {
				log.info("no Trade2Cst found for TradeGUID: " + tradeGUID);
				return false;
			}
			// 准备写
			bizCon = ADataSource.getConnection(ADataSource.SCHEMA_BIZ);
			String insertSql = CstUtil.formatInsertSql("s_Trade2Cst", fields);
			bizPstmt = bizCon.prepareStatement(insertSql);
			int columns = fields.split(",").length;
			boolean hasOne = false;

			// 同一笔交易，只能在同一项目，只取一次
			String projCode = getProjectCode(myCon, projGUID);
			// 取关联房产信息
			RoomInfo roomInfo = getRoomInfo(myCon, roomGUID);

			do {
				// 根据明源GUID查找数据中心客户GUID
				String oriCstGUID = rs.getString("CstGUID");
				long uid = CstUtil.getCstGUIDByOriGuid(bizCon, oriCstGUID);
				if (uid == 0) {
					// 查不到，则到明源查义主档记录的身份证或手机，再反查数据中心
					log.info("order no CstGUID found for:" + oriCstGUID
							+ ", retrieve again");
					uid = CstUtil.retrieveMemship(oriCstGUID);
				}
				// 更新售前客户状态
				// 仍未找到，则表示匹配不上该客户，即被彻底洗掉了，且现存主档也不规范
				if (uid == 0) {
					log.warn("SYNC_WARN[Order]关联客户在数据中心未找到[" + oriCstGUID + "]");
					continue;
				}
				if (save) {
					try {
						// 保存关联表
						for (int i = 1; i <= columns; i++) {
							bizPstmt.setString(i, rs.getString(i));
						}
						bizPstmt.executeUpdate();
					} catch (Exception e) {
					}
				} //
				
				String comment = "认购";
				//====== 更新客户状态开始==========
				//String[]：RecommendGUID,cstStatus,tradeGUID,amount,tradeStatusTrack,tradeStatus
				String[] recommend=RecommendUtil.getAgentCstRecommed(bizCon,uid,projCode);
				log.info("cstGUID:"+uid+",recommend:"+((recommend!=null)?Arrays.toString(recommend):"")+",reason:"+reason);
				if(recommend!=null){
					log.info("save:"+save+",state:"+state+",cstGUID:"+uid+",reason:"+reason);
					//满足以下条件：
					//1.没有处理过的
					//2.交易状态是变更价格的
					//3.交易状态是作废、换房、退房等等需要删除的，且没有处理过的订单
					if(!save || state==C.TRADE_STATUS_MODIFY || state==C.TRADE_STATUS_NEW ||
							(state!=C.TRADE_STATUS_NONE && state!=C.TRADE_STATUS_MODIFY && recommend[2].contains(tradeGUID))){
						log.info("start updateCstStatusForTrade:"+uid);
						String closeReason=null;
						if (reason==null||reason.equals("") || reason.equals("0") || reason.equalsIgnoreCase("NULL")) {
							closeReason="";
						}else{
							closeReason="("+reason+")";
						}
						RecommendUtil.updateCstStatusForTrade(bizCon,uid,recommend[0],
								recommend[2],Float.valueOf(recommend[3]),recommend[4],
								C.FOLLOW_ORDER,time,
								tradeGUID,total,updateMoney,
								comment+closeReason,state);
					}
				}else{
					//log.warn("SYNC_WARN[03]关联客户推荐记录未找到[cstGUID=" + uid+",projCode="+projCode + "]");
				}
				//====== 更新客户状态接结束==========
				
				if (hasOne) { // 因为是同一笔交易查出来的，因此只其中一个算金额
					comment = "联名认购";
				}
				if (reason != null && reason.trim().length() > 0) {
					comment += ":" + reason.trim();
				}
				String recommendGUID = recommend == null ? "" : recommend[0];
				if (save) { 
					// 如果保存，则保存跟进记录；保存跟进记录的同时会记录cst_Flag_XX日志
					FollowRecord fr = new FollowRecord();
					fr.setCstGUID(uid);
					fr.setOriCstGUID(oriCstGUID);
					fr.setProjGUID(projGUID);
					fr.setProjCode(projCode);
					fr.setReceiveType("认购");
					fr.setFollowType(C.FOLLOW_ORDER);
					fr.setCstType(4);
					fr.setReceiveDate(time);
					fr.setTotal(total);
					fr.setTradeGUID(tradeGUID);
					fr.setComment(comment);
					fr.setRoomGUID(roomGUID);
					fr.setRoom(roomInfo.getRoom());
					fr.setHuxing(roomInfo.getHuxing());
					fr.setRefGUID(OrderGUID);
					fr.setRecommendGUID(recommendGUID);
					TradeUtil.saveFollowRecord(bizCon, fr, log);
				} else {
					// 如果不保存，则也需要记录cst_Flag_XX日志，即变更日志
					// 且必须在不保存跟进时执行此段保存，否则会重复保存
//					String recommendGUID = RecommendUtil
//							.getLatestRecommendGUID(bizCon, uid, projCode);
					FlagLogBean flb = new FlagLogBean();
					flb.setAppid(C.DC_APPID);
					flb.setCstGUID(uid);
					flb.setOriCstGUID(oriCstGUID);
					flb.setProjCode(projCode);
					flb.setFlag(C.FLAG_ORDER_CHANGE);
					flb.setFlagTime(time);
					flb.setComment(comment);
					flb.setRoomGUID(roomGUID);
					flb.setRecommendGUID(recommendGUID);
					flb.setSubStatus(reason);
					flb.setRefGUID(OrderGUID);
					FlagLog.saveFlagLog(flb); // 保存日志
				}
				hasOne = true;
				if (TradeUtil.shouldNotice(state)) {
					// 判断是否需要通知，需要通知的状态为：新增、退房、换房、作废、取消/撤消
					TradeUtil.sendNotice(C.NOTICE_ORDER, 	// 消息类型
							uid,				// 客户GUID 
							projCode, 			// 项目编号
							time, 				// 时间
							total, 				// 金额
							C.FOLLOW_ORDER,		// 跟进类型
							comment, 			// 备注说明
							state,				// 状态
							roomGUID,			// 房间GUID
							roomInfo.getRoom(),	// 房号
							roomInfo.getHuxing());
				}
			} while (rs.next());// end while (rs.next())
			return hasOne;
		} catch (Exception e) {
			log.error("order handle Trade2Cst error:" + e.getMessage(), e);
			return false;
		} finally {
			ADataSource.close(rs, myPstmt, myCon);
			ADataSource.close(null, bizPstmt, bizCon);
		}
	} //~syncTrade2Cst
	//Object[]:ModiDate(Timestamp) total(float)
	Object[] exists(Connection con, String OrderGUID) {
		Object[] result=new Object[3];
		String sql = "select ModiDate,total,CloseDate from s_Order where OrderGUID=?";
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, OrderGUID);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				result[0]=rs.getTimestamp(1);
				result[1]=rs.getDouble(2);
				result[2]=rs.getTimestamp(3);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			ADataSource.close(rs, pstmt, null);
		}
		return result;
	}
} ///~
